# ~*~ coding: utf-8 ~*~
import json
import os
import importlib,sys
importlib.reload(sys)
import cx_Oracle
import pymysql
from resources.models import NewServer,ServerUser
from devops.models import AutoReCovery,MonitorConfig
from alert.models import AlertConfig


class DbLink():
	def __init__(self,):
		pass
	def Asset(self,id):
		s = NewServer.objects.get(id=id)
		hostname = s.hostname
		port = s.port
		username = s.server_user.username
		password = s.server_user.password
		ip = s.ip_inner
		private_key = s.server_user.privatekey

		asset = {'hostname': hostname,'ip': ip,'port': port, 'username': username, 'password': password,
				 'private_key':private_key}
		return asset

class MySQLdb():
	def __init__(self,username,password,ip,db=None,port=3306,charset='utf8'):
		self.ip = ip
		self.username = username
		self.password = password
		self.port = port
		self.charset = charset
		self.db = db
		self.timeout = 300

		self.con = pymysql.connect(host=self.ip, user=self.username,port=self.port,
								   passwd=self.password, charset=self.charset,
								   db=self.db,connect_timeout=self.timeout)
		#self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
		self.cursor = self.con.cursor()

	def GetStatus(self):
		sql = 'show global status'
		self.cursor.execute(sql)
		result = self.cursor.fetchall()
		rs = {}
		for i in result:
			rs[i[0]] = i[1]
		return rs

	def open_files_limit(self):
		sql = "show global variables like 'open_files_limit'"
		self.cursor.execute(sql)
		result = self.cursor.fetchone()
		rs = {result[0]:result[1]}
		return rs

	def max_connections(self):
		sql = "show variables like 'max_connections'"
		self.cursor.execute(sql)
		result = self.cursor.fetchone()
		rs = {result[0]:result[1]}
		return rs

	def table_open_cache(self):
		sql = "show variables like 'table_open_cache'"
		self.cursor.execute(sql)
		result = self.cursor.fetchone()
		rs = {result[0]:result[1]}
		return rs

	def SlaveStatus(self):
		sql = 'show slave status'
		self.cursor.execute(sql)
		result = self.cursor.fetchall()
		rs = {}
		if result == ():
			return rs

		for i in result:
			rs[i[0]] = i[1]
		return rs

	def close(self):
		self.cursor.close()
		self.con.close()

class MySQLdbD():
	def __init__(self,username,password,ip,db=None,port=3306,charset='utf8'):
		self.ip = ip
		self.username = username
		self.password = password
		self.port = port
		self.charset = charset
		self.db = db
		self.timeout = 300

		self.con = pymysql.connect(host=self.ip, user=self.username,port=self.port,
								   passwd=self.password, charset=self.charset,
								   db=self.db,connect_timeout=self.timeout)
		self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
		#self.cursor = self.con.cursor()

	def SlowLog(self,id,stime,etime,typev,sequencev):



		sql = '''
			SELECT `a`.`checksum`,
				 `a`.`fingerprint`,
				 `a`.`sample`,
				 `a`.`first_seen`, 
				 `a`.`last_seen`,
				 `b`.`serverid_max`, 
				 `b`.`db_max`,
				 `b`.`user_max`, 
				 `b`.`ts_min`,
				 `b`.`ts_max`,
				 sum(b.ts_cnt) ts_cnt,
				 sum(b.Query_time_sum)/sum(b.ts_cnt) Query_time_avg,
				 max(b.Query_time_max) Query_time_max,
				 min(b.Query_time_min) Query_time_min,
				 `b`.`Query_time_sum` Query_time_sum,
				 max(b.Lock_time_max) Lock_time_max,
				 min(b.Lock_time_min) Lock_time_min,
				 sum(b.Lock_time_sum) Lock_time_sum 
			FROM `dbmonitor_slowqueryreview` a 
				JOIN `dbmonitor_slowqueryreviewhistory` b 
				ON `a`.`checksum`=`b`.`checksum` 
			WHERE 
				`serverid_max` = {0}
				AND `last_seen` >= '{1}' 
				AND `last_seen` <= '{2}' 
				AND `a`.`sample` != 'commit'
				AND `b`.`db_max` != 'information_schema' 
				GROUP BY `a`.`checksum` 
				ORDER BY `{3}` 
				{4} LIMIT 25 
				
		'''.format(id,stime,etime,typev,sequencev)

		self.cursor.execute(sql)
		result = self.cursor.fetchall()

		rs = {}
		if result == ():
			return rs
		return result

	def SlowLogDetail(self,checksum):
		sql = '''
			select 
				b.db_max as db_max,
				b.user_max as user_max,
				b.checksum as checksum,
				a.first_seen as  first_seen,
				a.last_seen as last_seen,
				a.fingerprint as fingerprint,
				a.sample as sample,
				b.ts_cnt as ts_cnt,
				b.Query_time_sum as Query_time_sum,
				b.Query_time_min as Query_time_min,
				b.Query_time_max as Query_time_max,
				b.Query_time_pct_95 as Query_time_pct_95,
				b.Query_time_stddev as Query_time_stddev,
				b.Query_time_median as Query_time_median,
				
				b.Lock_time_sum as Lock_time_sum,
				b.Lock_time_min as Lock_time_min,
				b.Lock_time_max as Lock_time_max,
				b.Lock_time_pct_95 as Lock_time_pct_95,
				b.Lock_time_stddev as Lock_time_stddev,
				b.Lock_time_median as Lock_time_median,
				
				b.Rows_sent_sum as Rows_sent_sum,
				b.Rows_sent_min as Rows_sent_min,
				b.Rows_sent_max as Rows_sent_max,
				b.Rows_sent_pct_95 as Rows_sent_pct_95,
				b.Rows_sent_stddev as Rows_sent_stddev,
				b.Rows_sent_median as Rows_sent_median,
				
				b.Rows_examined_sum as Rows_examined_sum,
				b.Rows_examined_min as Rows_examined_min,
				b.Rows_examined_max as Rows_examined_max,
				b.Rows_examined_pct_95 as Rows_examined_pct_95,
				b.Rows_examined_stddev as Rows_examined_stddev,
				b.Rows_examined_median as Rows_examined_median
				
				
				 
			from `dbmonitor_slowqueryreview` a
			JOIN `dbmonitor_slowqueryreviewhistory` b 
			ON `a`.`checksum`=`b`.`checksum` 
			where a.checksum='{0}'
			group by a.checksum 
			order by a.last_seen desc limit 1
			
		'''.format(checksum)

		self.cursor.execute(sql)
		result = self.cursor.fetchall()

		rs = {}
		if result == ():
			return rs
		return result



	def close(self):
		self.cursor.close()
		self.con.close()


class OracleDB():
	def __init__(self, username, password, ip,port=1521,case=None):
		self.ip = ip
		self.username = username
		self.password = password
		self.port = port
		self.case = case
		os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
		link = '%s/%s@%s:%s/%s'%(self.username,self.password,self.ip,self.port,self.case)
		self.conn = cx_Oracle.connect(link)
		self.cursor = self.conn.cursor()

	def TableSpace(self):

		sql = '''
			SELECT a.tablespace_name tablespace_name,
			   total / (1024 * 1024 * 1024) total_space,
			   free / (1024 * 1024 * 1024) free_space,
			   (total - free) / (1024 * 1024 * 1024) use_space,
			   round((total - free) / total, 4) * 100 use_rate
		    FROM (SELECT tablespace_name, SUM(bytes) free
				  FROM dba_free_space
				 GROUP BY tablespace_name) a,
			   (SELECT tablespace_name, SUM(bytes) total
				  FROM dba_data_files
				 GROUP BY tablespace_name) b 
		    WHERE a.tablespace_name = b.tablespace_name
			'''

		sql1 = 'select * from sm_user'

		self.cursor.execute(sql)
		result = self.cursor.fetchall()

		cols = [d[0] for d in self.cursor.description]

		rs = []

		for row in result:
			b = dict(zip(cols, row))
			rs.append(b)
		return rs

	def close(self):
		self.cursor.close()
		self.con.close()

